package leaveSystem.dao.mysql;


import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import leaveSystem.dao.DAOException;
import leaveSystem.dao.EmployeeWorkscheduleDAO;
import leaveSystem.model.EmployeeWorkschedule;

public class EmployeeWorkscheduleDAOImpl implements EmployeeWorkscheduleDAO {

    public EmployeeWorkschedule createValueObject() {
          return new EmployeeWorkschedule();
    }


    public List<EmployeeWorkschedule> loadAll(Connection conn) throws SQLException {

          String sql = "SELECT * FROM leaveapp.employee_workschedule ORDER BY workscheduleid ASC ";
          List<EmployeeWorkschedule> searchResults = listQuery(conn, conn.prepareStatement(sql));

          return searchResults;
    }


    public void insertEmployeeWorkschedule(Connection conn, EmployeeWorkschedule valueObject) throws SQLException {

          String sql = "";
          PreparedStatement stmt = null;
          ResultSet result = null;

          try {
               sql = "INSERT INTO leaveapp.employee_workschedule ( workscheduleid, employeeid, day, "
               + "startTime, endtime, compensationHours, "
               + "Remarks, createdBy, createdTimeStamp, "
               + "updatedBy, updatedTimeStamp) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ";
               stmt = conn.prepareStatement(sql);

               stmt.setInt(1, valueObject.getWorkscheduleId()); 
               stmt.setInt(2, valueObject.getEmployeeId()); 
               stmt.setDate(3, (Date) valueObject.getDay()); 
               stmt.setDate(4, (Date) valueObject.getStartTime()); 
               stmt.setDate(5, (Date) valueObject.getEndTime()); 
               stmt.setInt(6, valueObject.getCompensationHours()); 
               stmt.setString(7, valueObject.getRemarks()); 
               stmt.setString(8, valueObject.getCreatedBy()); 
               stmt.setDate(9, (Date) valueObject.getCreatedTimeStamp()); 
               stmt.setString(10, valueObject.getUpdatedBy()); 
               stmt.setDate(11, (Date) valueObject.getUpdatedTimeStamp()); 

               int rowcount = databaseUpdate(conn, stmt);
               if (rowcount != 1) {
                    //System.out.println("PrimaryKey Error when updating DB!");
                    throw new SQLException("PrimaryKey Error when updating DB!");
               }

          } finally {
              if (stmt != null)
                  stmt.close();
          }


    }

    public void updateEmployeeWorkschedule(Connection conn, EmployeeWorkschedule valueObject) throws DAOException, SQLException {

          String sql = "UPDATE leaveapp.employee_workschedule SET employeeid = ?, day = ?, startTime = ?, "
               + "endtime = ?, compensationHours = ?, Remarks = ?, "
               + "createdBy = ?, createdTimeStamp = ?, updatedBy = ?, "
               + "updatedTimeStamp = ? WHERE (workscheduleid = ? ) ";
          PreparedStatement stmt = null;

          try {
              stmt = conn.prepareStatement(sql);
              stmt.setInt(1, valueObject.getEmployeeId()); 
              stmt.setDate(2, (Date) valueObject.getDay()); 
              stmt.setDate(3, (Date) valueObject.getStartTime()); 
              stmt.setDate(4, (Date) valueObject.getEndTime()); 
              stmt.setInt(5, valueObject.getCompensationHours()); 
              stmt.setString(6, valueObject.getRemarks()); 
              stmt.setString(7, valueObject.getCreatedBy()); 
              stmt.setDate(8, (Date) valueObject.getCreatedTimeStamp()); 
              stmt.setString(9, valueObject.getUpdatedBy()); 
              stmt.setDate(10, (Date) valueObject.getUpdatedTimeStamp()); 

              stmt.setInt(11, valueObject.getWorkscheduleId()); 

              int rowcount = databaseUpdate(conn, stmt);
              if (rowcount == 0) {
                   //System.out.println("Object could not be saved! (PrimaryKey not found)");
                   throw new DAOException("Object could not be saved! (PrimaryKey not found)");
              }
              if (rowcount > 1) {
                   //System.out.println("PrimaryKey Error when updating DB! (Many objects were affected!)");
                   throw new SQLException("PrimaryKey Error when updating DB! (Many objects were affected!)");
              }
          } finally {
              if (stmt != null)
                  stmt.close();
          }
    }

    public void deleteEmployeeWorkschedule(Connection conn, EmployeeWorkschedule valueObject) throws DAOException, SQLException {

          String sql = "DELETE FROM leaveapp.employee_workschedule WHERE (workscheduleid = ? ) ";
          PreparedStatement stmt = null;

          try {
              stmt = conn.prepareStatement(sql);
              stmt.setInt(1, valueObject.getWorkscheduleId()); 

              int rowcount = databaseUpdate(conn, stmt);
              if (rowcount == 0) {
                   //System.out.println("Object could not be deleted (PrimaryKey not found)");
                   throw new DAOException("Object could not be deleted! (PrimaryKey not found)");
              }
              if (rowcount > 1) {
                   //System.out.println("PrimaryKey Error when updating DB! (Many objects were deleted!)");
                   throw new SQLException("PrimaryKey Error when updating DB! (Many objects were deleted!)");
              }
          } finally {
              if (stmt != null)
                  stmt.close();
          }
    }

    public void deleteAllEmployeeWorkschedule(Connection conn) throws SQLException {

          String sql = "DELETE FROM leaveapp.employee_workschedule";
          PreparedStatement stmt = null;

          try {
              stmt = conn.prepareStatement(sql);
              int rowcount = databaseUpdate(conn, stmt);
          } finally {
              if (stmt != null)
                  stmt.close();
          }
    }

   /* public int countAll(Connection conn) throws SQLException {

          String sql = "SELECT count(*) FROM employee_workschedule";
          PreparedStatement stmt = null;
          ResultSet result = null;
          int allRows = 0;

          try {
              stmt = conn.prepareStatement(sql);
              result = stmt.executeQuery();

              if (result.next())
                  allRows = result.getInt(1);
          } finally {
              if (result != null)
                  result.close();
              if (stmt != null)
                  stmt.close();
          }
          return allRows;
    }*/

    public List<EmployeeWorkschedule> findEmployeeWorkschedule(Connection conn, EmployeeWorkschedule valueObject) throws SQLException {

          List<EmployeeWorkschedule> searchResults;

          boolean first = true;
          StringBuffer sql = new StringBuffer("SELECT * FROM leaveapp.employee_workschedule WHERE 1=1 ");

          if (valueObject!=null && valueObject.getWorkscheduleId() != 0) {
              if (first) { first = false; }
              sql.append("AND workscheduleid = ").append(valueObject.getWorkscheduleId()).append(" ");
          }

          if (valueObject!=null && valueObject.getEmployeeId() != 0) {
              if (first) { first = false; }
              sql.append("AND employeeid = ").append(valueObject.getEmployeeId()).append(" ");
          }

          if (valueObject!=null && valueObject.getDay() != null) {
              if (first) { first = false; }
              sql.append("AND day = '").append(valueObject.getDay()).append("' ");
          }

          if (valueObject!=null && valueObject.getStartTime() != null) {
              if (first) { first = false; }
              sql.append("AND startTime = '").append(valueObject.getStartTime()).append("' ");
          }

          if (valueObject!=null && valueObject.getEndTime() != null) {
              if (first) { first = false; }
              sql.append("AND endtime = '").append(valueObject.getEndTime()).append("' ");
          }

          if (valueObject!=null && valueObject.getCompensationHours() != 0) {
              if (first) { first = false; }
              sql.append("AND compensationHours = ").append(valueObject.getCompensationHours()).append(" ");
          }

          if (valueObject!=null && valueObject.getRemarks() != null) {
              if (first) { first = false; }
              sql.append("AND Remarks LIKE '").append(valueObject.getRemarks()).append("%' ");
          }

          if (valueObject!=null && valueObject.getCreatedBy() != null) {
              if (first) { first = false; }
              sql.append("AND createdBy LIKE '").append(valueObject.getCreatedBy()).append("%' ");
          }

          if (valueObject!=null && valueObject.getCreatedTimeStamp() != null) {
              if (first) { first = false; }
              sql.append("AND createdTimeStamp = '").append(valueObject.getCreatedTimeStamp()).append("' ");
          }

          if (valueObject!=null && valueObject.getUpdatedBy() != null) {
              if (first) { first = false; }
              sql.append("AND updatedBy LIKE '").append(valueObject.getUpdatedBy()).append("%' ");
          }

          if (valueObject!=null && valueObject.getUpdatedTimeStamp() != null) {
              if (first) { first = false; }
              sql.append("AND updatedTimeStamp = '").append(valueObject.getUpdatedTimeStamp()).append("' ");
          }


          sql.append("ORDER BY workscheduleid ASC ");

          // Prevent accidential full table results.
          // Use loadAll if all rows must be returned.
          if (first)
               searchResults = new ArrayList<EmployeeWorkschedule>();
          else
               searchResults = listQuery(conn, conn.prepareStatement(sql.toString()));

          return searchResults;
    }

    public String getDaogenVersion() {
        return null;
    }

    protected int databaseUpdate(Connection conn, PreparedStatement stmt) throws SQLException {

          int result = stmt.executeUpdate();

          return result;
    }
/*
    protected void singleQuery(Connection conn, PreparedStatement stmt, EmployeeWorkschedule valueObject) 
          throws DAOException, SQLException {

          ResultSet result = null;

          try {
              result = stmt.executeQuery();

              if (result.next()) {

                   valueObject.setWorkscheduleId(result.getInt("workscheduleid")); 
                   valueObject.setEmployeeId(result.getInt("employeeid")); 
                   valueObject.setDay(result.getDate("day")); 
                   valueObject.setStartTime(result.getDate("startTime")); 
                   valueObject.setEndTime(result.getDate("endtime")); 
                   valueObject.setCompensationHours(result.getInt("compensationHours")); 
                   valueObject.setRemarks(result.getString("Remarks")); 
                   valueObject.setCreatedBy(result.getString("createdBy")); 
                   valueObject.setCreatedTimeStamp(result.getDate("createdTimeStamp")); 
                   valueObject.setUpdatedBy(result.getString("updatedBy")); 
                   valueObject.setUpdatedTimeStamp(result.getDate("updatedTimeStamp")); 

              } else {
                    //System.out.println("EmployeeWorkschedule Object Not Found!");
                    throw new DAOException("EmployeeWorkschedule Object Not Found!");
              }
          } finally {
              if (result != null)
                  result.close();
              if (stmt != null)
                  stmt.close();
          }
    }
*/
    protected List<EmployeeWorkschedule> listQuery(Connection conn, PreparedStatement stmt) throws SQLException {

          ArrayList<EmployeeWorkschedule> searchResults = new ArrayList<EmployeeWorkschedule>();
          ResultSet result = null;

          try {
              result = stmt.executeQuery();

              while (result.next()) {
                   EmployeeWorkschedule temp = createValueObject();

                   temp.setWorkscheduleId(result.getInt("workscheduleid")); 
                   temp.setEmployeeId(result.getInt("employeeid")); 
                   temp.setDay(result.getDate("day")); 
                   temp.setStartTime(result.getDate("startTime")); 
                   temp.setEndTime(result.getDate("endtime")); 
                   temp.setCompensationHours(result.getInt("compensationHours")); 
                   temp.setRemarks(result.getString("Remarks")); 
                   temp.setCreatedBy(result.getString("createdBy")); 
                   temp.setCreatedTimeStamp(result.getDate("createdTimeStamp")); 
                   temp.setUpdatedBy(result.getString("updatedBy")); 
                   temp.setUpdatedTimeStamp(result.getDate("updatedTimeStamp")); 

                   searchResults.add(temp);
              }

          } finally {
              if (result != null)
                  result.close();
              if (stmt != null)
                  stmt.close();
          }

          return (List)searchResults;
    }

	}
